﻿using System.Collections.Generic;
using System.Data.SQLite;
using StudentManagementSystem.Models;

namespace StudentManagementSystem.Data
{
    public class SchoolRepository
    {
        private readonly string _connectionString;


        public SchoolRepository(string connectionString)
        {
            _connectionString = connectionString;
        }
        public void Add(School school)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "INSERT INTO Schools (Name, Address) VALUES (@Name, @Address); " +
                "SELECT last_insert_rowid();", connection);

            command.Parameters.AddWithValue("@Name", school.Name);
            command.Parameters.AddWithValue("@Address", school.Address);

            school.Id = (int)(long)command.ExecuteScalar();
        }

        public IEnumerable<School> GetAll()
        {
            var schools = new List<School>();

            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("SELECT * FROM Schools", connection);
            using var reader = command.ExecuteReader();

            while (reader.Read())
            {
                schools.Add(new School
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Address = reader.IsDBNull(2) ? null : reader.GetString(2)
                });
            }

            return schools;
        }

        public School GetById(int id)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("SELECT * FROM Schools WHERE Id = @Id", connection);
            command.Parameters.AddWithValue("@Id", id);

            using var reader = command.ExecuteReader();

            if (reader.Read())
            {
                return new School
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Address = reader.IsDBNull(2) ? null : reader.GetString(2)
                };
            }

            return null;
        }

        public void Update(School school)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "UPDATE Schools SET Name = @Name, Address = @Address WHERE Id = @Id", connection);

            command.Parameters.AddWithValue("@Id", school.Id);
            command.Parameters.AddWithValue("@Name", school.Name);
            command.Parameters.AddWithValue("@Address", school.Address);

            command.ExecuteNonQuery();
        }

        public void Delete(int id)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("DELETE FROM Schools WHERE Id = @Id", connection);
            command.Parameters.AddWithValue("@Id", id);

            command.ExecuteNonQuery();
        }
    }
}